package com.ejia.server;

import java.nio.channels.NonWritableChannelException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ejia.dao.BaseDao;
import com.ejia.pojo.fytype;
import com.ejia.pojo.money;
import com.ejia.pojo.user;

public class MoneyService {
	public static double sum() {
		String sql="select sum(m.number * t.fyPrice) FROM ejiadb.tb_money m JOIN ejiadb.tb_fytype t WHERE t.fyId = m.typeId";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		try {
			while (rSet.next()) {
				return rSet.getDouble(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public static double arrears() {
		String sql="select sum(m.number * t.fyPrice) FROM ejiadb.tb_money m JOIN ejiadb.tb_fytype t WHERE t.fyId = m.typeId and m.status=0";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		try {
			while (rSet.next()) {
				return rSet.getDouble(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	
	public static double fyprice(String name) {
		String sql="select fyprice from tb_fytype where fyname=?";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,name);
		try {
			while (rSet.next()) {
				return rSet.getDouble(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	
	
	
	public static ArrayList<money> find() {
		String sql="SELECT\n" + 
				"	m.mid AS mid,\n" + 
				"	u.RealName AS RealName,\n" + 
				"	t.fyName AS fyName,\n" + 
				"	m.startDate AS startDate,\n" + 
				"	m.endDate AS endDate,(\n" + 
				"		m.number * t.fyPrice \n" + 
				"	) AS price \n" + 
				"FROM\n" + 
				"	ejiadb.tb_money m\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"	JOIN ejiadb.tb_fytype t \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			t.fyId = m.typeId \n" + 
				"			) \n" + 
				"	AND ( u.Id = m.userId ) \n" + 
				"	AND ( m.STATUS = 0 ))";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<money> moneys = new ArrayList<>();
		money money=null;
		user user=null;
		fytype fytype=null;
		try {
			while (rSet.next()) {
				money=new money();
				user=new user();
				fytype=new fytype();
				fytype.setFyname(rSet.getString("fyName"));
				user.setUid(rSet.getString("RealName"));
				money.setTypeid(fytype);
				money.setUserid(user);
				money.setMid(rSet.getInt("mid"));
				money.setStartDate(rSet.getDate("startDate"));
				money.setEndDate(rSet.getDate("endDate"));
				money.setNumber(rSet.getDouble("price"));
				moneys.add(money);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return moneys;
		
	}
	
	public static ArrayList<money> WYJFMX() {
		String sql="SELECT\n" + 
				"	m.mid AS mid,\n" + 
				"	u.RealName AS RealName,\n" + 
				"	t.fyName AS fyName,\n" + 
				"	m.number AS number,\n" + 
				"	concat( t.fyPrice, t.unit ) AS unit,\n" + 
				"	m.startDate AS startDate,\n" + 
				"	m.endDate AS endDate,(\n" + 
				"		m.number * t.fyPrice \n" + 
				"	) AS price,\n" + 
				"	m.STATUS AS statu \n" + 
				"FROM\n" + 
				"	ejiadb.tb_money m\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"	JOIN ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_fytype t \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			t.fyId = m.typeId \n" + 
				"			) \n" + 
				"	AND ( u.Id = m.userId ) \n" + 
				"	AND ( h.hId = u.hid ))";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<money> moneys = new ArrayList<>();
		money money=null;
		user user=null;
		fytype fytype=null;
		try {
			while (rSet.next()) {
				money=new money();
				user=new user();
				fytype=new fytype();
				money.setMid(rSet.getInt("mid"));
				user.setRealname(rSet.getString("Realname"));
				money.setUserid(user);
				fytype.setFyname(rSet.getString("fyName"));
				money.setNumber(rSet.getInt("number"));
				fytype.setUnit(rSet.getString("unit"));
				fytype.setFyprice(rSet.getDouble("price"));
				money.setTypeid(fytype);
				money.setStartDate(rSet.getDate("startDate"));
				money.setEndDate(rSet.getDate("endDate"));
				money.setStatus(rSet.getInt("statu"));
				moneys.add(money);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return moneys;
		
	}
	
	public static ArrayList<money> WYJFMXfind(String name) {
		String sql="SELECT\n" + 
				"	m.mid AS mid,\n" + 
				"	u.RealName AS RealName,\n" + 
				"	t.fyName AS fyName,\n" + 
				"	m.number AS number,\n" + 
				"	concat( t.fyPrice, t.unit ) AS unit,\n" + 
				"	m.startDate AS startDate,\n" + 
				"	m.endDate AS endDate,(\n" + 
				"		m.number * t.fyPrice \n" + 
				"	) AS price,\n" + 
				"	m.STATUS AS statu \n" + 
				"FROM\n" + 
				"	ejiadb.tb_money m\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"	JOIN ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_fytype t \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			t.fyId = m.typeId \n" + 
				"			) \n" + 
				"	AND ( u.Id = m.userId ) \n" + 
				"	AND ( h.hId = u.hid )"
				+ "and u.RealName like ?)";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,"%"+name+"%");
		ArrayList<money> moneys = new ArrayList<>();
		money money=null;
		user user=null;
		fytype fytype=null;
		try {
			while (rSet.next()) {
				money=new money();
				user=new user();
				fytype=new fytype();
				money.setMid(rSet.getInt("mid"));
				user.setRealname(rSet.getString("Realname"));
				money.setUserid(user);
				fytype.setFyname(rSet.getString("fyName"));
				money.setNumber(rSet.getInt("number"));
				fytype.setUnit(rSet.getString("unit"));
				fytype.setFyprice(rSet.getDouble("price"));
				money.setTypeid(fytype);
				money.setStartDate(rSet.getDate("startDate"));
				money.setEndDate(rSet.getDate("endDate"));
				money.setStatus(rSet.getInt("statu"));
				moneys.add(money);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return moneys;
		
	}
	
	public static boolean update(int id) {
		String sql="update tb_money set status=1 where mid=?";
		BaseDao baseDao=new BaseDao();
		return baseDao.execute(sql,id);
	}
	
	
	public static String unit(String name) {
		String sql="select SUBSTRING(unit,-1) as unit from tb_fytype where fyname=?";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,name);
		try {
			while (rSet.next()) {
				return rSet.getString("unit");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}
	
	
	
	public static boolean add(int userid,int typeid,String startdate,String enddate,double number,int status) {
		String sql="insert into tb_money(userid,typeid,startdate,enddate,number,status) values(?,?,?,?,?,?)";
		BaseDao baseDao=new BaseDao();
		return baseDao.execute(sql,userid,typeid,startdate,enddate,number,status);
	}
	
	
	public static boolean delete(int mid) {
		String sql="delete from tb_money where mid=?";
		return new BaseDao().execute(sql, mid);
	}
	
	
	
}
